set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_tmp.dm_whole_decide_duty_dynamic_dt
select
     waybill_no                             --运单号
    ,platform_effect                        --自有平台时效标准(兜底路由)
    ,ziyou_platform_effect                  --自由平台时效标准(推荐路由)
    ,if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
    ,if_doudi_platform_standard             --自有平台时效是否达成:1达成,0未达成(兜底路由)
    ,cainiao_platform_name                  --七星潭平台名称
    ,cainiao_platform_effect                --七星潭平台时效标准
    ,if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
    ,first_duty_type                        --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
    ,first_ziyou_duty_type                  --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
    ,if_intercept                           --是否拦截件:1是,0否
    ,if_reback                              --是否退回件:1是,0否
    ,if_transfer                            --是否转寄件:1是,0否
    ,start_city_code                        --始发城市编码
    ,start_city_name                        --始发城市名称
    ,sign_city_code                         --签收城市编码
    ,sign_city_name                         --签收城市名称
    ,taking_time                            --揽收时间
    ,deliver_time                           --出仓时间
    ,aging_sign_time                        --时效签收时间
    ,actual_sign_time                       --实际签收时间
    ,economic_circle_name                   --经济圈名称
    ,scan_site_no                           --当前站点编号
    ,scan_site_code                         --当前站点编码
    ,scan_site_name                         --当前站点名称
    ,scan_site_type                         --当前站点类型
    ,scan_site_arrival_time                 --当前站点到件时间
    ,scan_site_send_time                    --当前站点发件时间
    ,scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
    ,scan_site_actual_send_time             --当前站点发件任务号实际发车时间
    ,scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
    ,scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
    ,scan_site_seal_time                    --当前站点发件任务号封车时间
    ,scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
    ,scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
    ,scan_site_if_stop                      --当前站是否经停
    ,scan_site_if_bagg                      --当前站点是否建包扫描
    ,scan_site_arrival_bagg_code            --当前站点到件包号
    ,scan_site_arrival_bagg_name            --当前站点到件包名
    ,scan_site_send_bagg_code               --当前站点发件包号
    ,scan_site_send_bagg_name               --当前站点发件包名
    ,scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
    ,scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
    ,scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
    ,scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
    ,actual_diff_time                       --当前站点与下一站实际时间差(小时)
    ,actual_node                            --当前站点简称(实际节点)
    ,next_site_code                         --下一站编码
    ,next_site_name                         --下一站名称
    ,next_site_type                         --下一站类型
    ,next_site_arrival_time                 --下一站到件时间
    ,next_site_send_time                    --下一站发件时间
    ,next_site_actual_arrival_time          --下一站发件任务号到车时间
    ,next_site_actual_send_time             --下一站发件任务号发车时间
    ,next_site_seal_time                    --下一站发件任务号封车时间
    ,next_site_planned_departure_time       --下一站发件任务号规划发车时间
    ,next_site_planned_arrival_time         --下一站发件任务号规划到车时间
    ,next_site_if_stop                      --下一站发件是否经停
    ,pre_site_code                          --上一站编码
    ,pre_site_name                          --上一站名称
    ,pre_site_type                          --上一站类型
    ,pre_site_arrival_time                  --上一站到件时间
    ,pre_site_send_time                     --上一站发件时间
    ,pre_site_actual_arrival_time           --上一站发件任务号到车时间
    ,pre_site_actual_send_time              --上一站发件任务号发车时间
    ,pre_site_seal_time                     --上一站发件任务号封车时间
    ,pre_site_planned_departure_time        --上一站发件任务号规划发车时间
    ,pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
    ,pre_scan_if_stop                       --上一站是否经停
    ,first_center_actual_arrival_time       --始发中心实际到车时间
    ,first_center_actual_send_time          --始发中心实际发车时间
    ,first_network_code                     --始发网点编码
    ,first_network_name                     --始发网点名称
    ,sign_network_code                      --签收网点编码
    ,sign_network_name                      --签收网点名称
    ,last_network_code                      --目的网点编码
    ,last_network_name                      --目的网点名称
    ,first_code                             --一段码
    ,lasted_center_code                     --一段码目的中心编码
    ,second_code                            --二段码
    ,lasted_network_code                    --二段码目的网点
    ,type                                   --场景划分
    ,total_days                             --T+时效
    ,route_id                               --路由唯一键
    ,if_taking_off                          --是否截单后揽收
    ,actual_effect                          --动态时效 20230216新增
    ,pre_site_stop_actual_send_time         --经停站点上一站实际发车时间_20230227新增
    ,pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间_20230227新增
    ,next_site_stop_code                    --经停站点下一站点编码_20230227新增
    ,next_site_stop_name                    --经停站点下一站点名称_20230227新增
    ,next_site_stop_type                    --经停站点下一站点类型_20230227新增
    ,next_site_stop_actual_arrival_time     --经停站点下一站点实际到车时间_20230227新增
    ,next_site_stop_planned_arrival_time    --经停站点下一站点规划到车时间_20230227新增
    ,next_stop_site_actual_send_time        --下一经停站点规划发车时间_20230227新增
    ,next_stop_site_planned_departure_time  --下一经停站点规划到车时间_20230227新增
    ,next_stop_site_actual_arrival_time     --下一经停站点实际到车时间_20230227新增
    ,next_stop_site_planned_arrival_time    --下一经停站点实际发车时间_20230227新增
    ,next_l2_site_code                      --下下一站编码
    ,if_taitc                               --问题件抬头错标识
    ,order_source_code                      --订单来源编码
    ,order_source_name                      --订单来源名称
    ,if_reback_reach                        --退转拦截是否达成
    ,reback_apply_time                      --退转件申请时间
    ,intercept_registration_time            --拦截件登记时间
    ,cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
    ,ziyou_plan_sign_time                   --达成率自有规划签收时间
    ,taking_date                            --揽收日期(含截单后揽收)
    ,start_center_code                      --始发中心编码
    ,start_center_name                      --始发中心名称
    ,end_center_code                        --目的中心编码
    ,end_center_name                        --目的中心名称
    ,customer_code                          --客户编码
    ,customer_name                          --客户名称
    ,scan_site_bagg_user_code               --当前站点建包扫描员编号
    ,scan_site_bagg_user_name               --当前站点建包扫描员名称
    ,pre_site_stop_code                     --经停站点上一站编码
    ,pre_site_stop_name                     --经停站点上一站名称
    ,pre_site_stop_type                     --经停站点上一站类型
    ,scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
    ,dt
from (
    select

         waybill_no                             --运单号
        ,platform_effect                        --自有平台时效标准(兜底路由)
        ,ziyou_platform_effect                  --自由平台时效标准(推荐路由)
        ,if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
        ,case when aging_sign_time is null and datediff(current_date(),taking_date) >  platform_effect then 0
              when aging_sign_time is null and datediff(current_date(),taking_date) <= platform_effect then null
              when aging_sign_time is not null and datediff(to_date(aging_sign_time),taking_date) <= platform_effect then 1
              when aging_sign_time is not null and datediff(to_date(aging_sign_time),taking_date) >  platform_effect then 0
              end as if_doudi_platform_standard --自有平台时效是否达成:1达成,0未达成(兜底路由)
        ,cainiao_platform_name                  --七星潭平台名称
        ,cainiao_platform_effect                --七星潭平台时效标准
        ,if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
        ,case when if_cainiao_platform_pres_standard = 1 then null
              when scan_site_no = 1 and ziyou_platform_effect is null then '静态路由缺失'
              when scan_site_no = 1 and (actual_sign_time is not null and actual_sign_code <> last_network_code
              and if_reback <> 1 and if_transfer <> 1) or if_taitc = 1 then '地址解析错误'
              when scan_site_no = 1 and fast_total_days > cainiao_platform_effect then '静态慢于' --全部慢于才算慢于
              when scan_site_no = 1 and fast_total_days <= cainiao_platform_effect and total_days > cainiao_platform_effect then '静态慢于(多班次)' --20230920新增责任
              when scan_site_no = 1 and total_days + extra_time > cainiao_platform_effect  then '乡镇加时'
              end as first_duty_type --七星潭静态环节责任
        ,case when if_platform_standard= 1 then null
              when scan_site_no = 1 and (actual_sign_time is not null and actual_sign_code <> last_network_code
              and if_reback <> 1 and if_transfer <> 1) or if_taitc = 1 then '地址解析错误'
              end as first_ziyou_duty_type --自由平台静态环节责任责任
        ,if_intercept                           --是否拦截件:1是,0否
        ,if_reback                              --是否退回件:1是,0否
        ,if_transfer                            --是否转寄件:1是,0否
        ,start_city_code                        --始发城市编码
        ,start_city_name                        --始发城市名称
        ,sign_city_code                         --签收城市编码
        ,sign_city_name                         --签收城市名称
        ,taking_time                            --揽收时间
        ,deliver_time                           --出仓时间
        ,aging_sign_time                        --时效签收时间
        ,actual_sign_time                       --实际签收时间
        ,economic_circle_name                   --经济圈名称
        ,scan_site_no                           --当前站点编号
        ,scan_site_code                         --当前站点编码
        ,scan_site_name                         --当前站点名称
        ,scan_site_type                         --当前站点类型
        ,scan_site_arrival_time                 --当前站点到件时间
        ,scan_site_send_time                    --当前站点发件时间
        ,scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
        ,scan_site_actual_send_time             --当前站点发件任务号实际发车时间
        ,scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
        ,scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
        ,scan_site_seal_time                    --当前站点发件任务号封车时间
        ,scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
        ,scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
        ,scan_site_if_stop                      --当前站是否经停
        ,scan_site_if_bagg                      --当前站点是否建包扫描
        ,scan_site_arrival_bagg_code            --当前站点到件包号
        ,scan_site_arrival_bagg_name            --当前站点到件包名
        ,scan_site_send_bagg_code               --当前站点发件包号
        ,scan_site_send_bagg_name               --当前站点发件包名
        ,scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
        ,scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
        ,scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
        ,scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
        ,actual_diff_time                       --当前站点与下一站实际时间差(小时)
        ,actual_node                            --当前站点简称(实际节点)
        ,next_site_code                         --下一站编码
        ,next_site_name                         --下一站名称
        ,next_site_type                         --下一站类型
        ,next_site_arrival_time                 --下一站到件时间
        ,next_site_send_time                    --下一站发件时间
        ,next_site_actual_arrival_time          --下一站发件任务号到车时间
        ,next_site_actual_send_time             --下一站发件任务号发车时间
        ,next_site_seal_time                    --下一站发件任务号封车时间
        ,next_site_planned_departure_time       --下一站发件任务号规划发车时间
        ,next_site_planned_arrival_time         --下一站发件任务号规划到车时间
        ,next_site_if_stop                      --下一站发件是否经停
        ,pre_site_code                          --上一站编码
        ,pre_site_name                          --上一站名称
        ,pre_site_type                          --上一站类型
        ,pre_site_arrival_time                  --上一站到件时间
        ,pre_site_send_time                     --上一站发件时间
        ,pre_site_actual_arrival_time           --上一站发件任务号到车时间
        ,pre_site_actual_send_time              --上一站发件任务号发车时间
        ,pre_site_seal_time                     --上一站发件任务号封车时间
        ,pre_site_planned_departure_time        --上一站发件任务号规划发车时间
        ,pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
        ,pre_scan_if_stop                       --上一站是否经停
        ,first_center_actual_arrival_time       --始发中心实际到车时间
        ,first_center_actual_send_time          --始发中心实际发车时间
        ,first_network_code                     --始发网点编码
        ,first_network_name                     --始发网点名称
        ,sign_network_code                      --签收网点编码
        ,sign_network_name                      --签收网点名称
        ,last_network_code                      --目的网点编码
        ,last_network_name                      --目的网点名称
        ,first_code                             --一段码
        ,lasted_center_code                     --一段码目的中心编码
        ,second_code                            --二段码
        ,lasted_network_code                    --二段码目的网点
        ,type                                   --场景划分
        ,total_days                             --T+时效
        ,route_id                               --路由唯一键
        ,if_taking_off                          --是否截单后揽收
        ,actual_effect                          --动态时效 20230216新增
        ,pre_site_stop_actual_send_time         --经停站点上一站实际发车时间_20230227新增
        ,pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间_20230227新增
        ,next_site_stop_code                    --经停站点下一站点编码_20tt230227新增
        ,next_site_stop_name                    --经停站点下一站点名称_20230227新增
        ,next_site_stop_type                    --经停站点下一站点类型_20230227新增
        ,next_site_stop_actual_arrival_time     --经停站点下一站点实际到车时间_20230227新增
        ,next_site_stop_planned_arrival_time    --经停站点下一站点规划到车时间_20230227新增
        ,next_stop_site_actual_send_time        --下一经停站点规划发车时间_20230227新增
        ,next_stop_site_planned_departure_time  --下一经停站点规划到车时间_20230227新增
        ,next_stop_site_actual_arrival_time     --下一经停站点实际到车时间_20230227新增
        ,next_stop_site_planned_arrival_time    --下一经停站点实际发车时间_20230227新增
        ,next_l2_site_code                      --下下一站编码
        ,if_taitc                               --问题件抬头错标识
        ,order_source_code                      --订单来源编码
        ,order_source_name                      --订单来源名称
        ,if_reback_reach                        --退转拦截是否达成
        ,reback_apply_time                      --退转件申请时间
        ,intercept_registration_time            --拦截件登记时间
        ,cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
        ,ziyou_plan_sign_time                   --达成率自有规划签收时间
        ,taking_date                            --揽收日期(含截单后揽收)
        ,start_center_code                      --始发中心编码
        ,start_center_name                      --始发中心名称
        ,end_center_code                        --目的中心编码
        ,end_center_name                        --目的中心名称
        ,customer_code                          --客户编码
        ,customer_name                          --客户名称
        ,scan_site_bagg_user_code               --当前站点建包扫描员编号
        ,scan_site_bagg_user_name               --当前站点建包扫描员名称
        ,pre_site_stop_code                     --经停站点上一站编码
        ,pre_site_stop_name                     --经停站点上一站名称
        ,pre_site_stop_type                     --经停站点上一站类型
        ,scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
        ,dt
        ,row_number() over(partition by waybill_no,scan_site_no
                               order by if(unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time) <= 0, 1, 2)
                                       ,if(if_taking_off = 1,unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time), 0) desc --如果是截单后揽收,最当天最早的
                                       ,abs(unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time))
                                       ,total_days asc
                                       ,route_cnt desc
                                       ,scan_site_plan_send_time desc
                                       ,route_id desc) as rn
    from (
        select
             wide.*
            ,route.route_id                   as route_id   --路由唯一键
            ,route.total_days                 as total_days --T+时效
            ,route.total_days + if(wide.taking_date>to_date(wide.taking_time),1,0) as platform_effect --时效标准
            ,concat(date_add(wide.taking_date,route.send_span_days),' ',route.plan_send_time,':00') as scan_site_plan_send_time  --当前站点规划发车时间
            ,concat(wide.taking_date,' ',route.last_taking_time) as plan_last_taking_time       --当前站点规划揽收结束时间
            ,concat(wide.taking_date,' ',node.lasted_taking_time) as plan_lasted_taking_time   --当前站点规划最晚揽收结束时间
            ,count(1) over(partition by waybill_no,route_id) as route_cnt --节点数
            ,case when max_search_type = min_search_type then max_search_type else min_search_type end as fast_total_days --最快的T+时效
            ,case when taking_time > concat(wide.taking_date,' ',node.lasted_taking_time,':00') then 1 else 0 end as if_taking_off --是否截单后揽收
        from (
            select
                 *
                ,to_date(taking_time) as taking_date
            from jms_tmp.dm_whole_decide_duty_detail_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
              --   and if(least(nvl(scan_site_send_time,'2099-00-00 00:00:00'),nvl(scan_site_arrival_time,'2099-00-00 00:00:00')
              --               ,nvl(scan_site_actual_arrival_time,'2099-00-00 00:00:00'),nvl(scan_site_actual_send_time,'2099-00-00 00:00:00')) ='2099-00-00 00:00:00','0000-00-00 00:00:00',
              --          least(nvl(scan_site_send_time,'2099-00-00 00:00:00'),nvl(scan_site_arrival_time,'2099-00-00 00:00:00')
              --               ,nvl(scan_site_actual_arrival_time,'2099-00-00 00:00:00'),nvl(scan_site_actual_send_time,'2099-00-00 00:00:00'))) <=
              --          least(nvl(reback_apply_time,'2099-00-00 00:00:00'),nvl(intercept_registration_time,'2099-00-00 00:00:00')) --退转拦截件后轨迹不判责,但要过滤掉轨迹
              --  and (if_platform_standard = 0 or if_cainiao_platform_pres_standard = 0) --重新计算自由平台或七星潭未达成票数 20230330要求展示，单不判责
        ) wide
        left join (
            select
                 in_from_code
                ,out_to_code
                ,max(search_type) as max_search_type --网点-网点-揽收时间段 最大的时效
                ,min(search_type) as min_search_type --网点-网点-揽收时间段 最小的时效
                ,max(last_taking_time) as lasted_taking_time --最晚的揽收结束时间
                ,dt
            from (
                select *
                from (
                    select
                         in_from_code
                        ,out_to_code
                        ,in_from_name
                        ,out_to_name
                        ,last_taking_time
                        ,search_type
                        ,row_number() over(partition by in_from_code,out_to_code,last_taking_time,dt order by search_type) as rn
                        ,dt
                    from jms_dm.dm_route_whole_node_base_dt
                    where dt between date_sub('{{ execution_date | cst_ds }}',21) and '{{ execution_date | cst_ds }}'
                ) a where a.rn = 1
            ) a group by in_from_code
                        ,out_to_code
                        ,dt
        ) node on node.in_from_code = wide.first_network_code  --始发网点
              and node.out_to_code = wide.last_network_code    --目的网点
              and date_add(node.dt,1) = wide.taking_date --揽收日期(含截单后揽收)
        left join (
            select *
            from jms_dm.dm_route_whole_step_dt route --兜底路由轨迹表
            --where dt = '{{ execution_date | cst_ds }}'
            where dt between date_sub('{{ execution_date | cst_ds }}',21) and '{{ execution_date | cst_ds }}'
            and last_taking_time is not null
        ) route on route.scan_site_code = wide.scan_site_code  --站点相等
               and route.scan_site_no = wide.scan_site_no    --轨迹相等
               and nvl(route.pre_site_code   ,route.first_network_code) = nvl(wide.pre_site_code   ,wide.first_network_code ) --上一站
               and nvl(route.pre_l2_site_code,route.first_network_code) = nvl(wide.pre_l2_site_code,wide.first_network_code ) --上上一站
               and nvl(route.pre_l3_site_code,route.first_network_code) = nvl(wide.pre_l3_site_code,wide.first_network_code ) --上上上一站
               and nvl(route.pre_l4_site_code,route.first_network_code) = nvl(wide.pre_l4_site_code,wide.first_network_code ) --上上上上一站
               and route.first_network_code = wide.first_network_code  --始发网点
               and route.last_network_code = wide.last_network_code    --目的网点
               and date_add(route.dt,1) = wide.taking_date --揽收日期(含截单后揽收)
    ) a
) a where a.rn = 1
distribute by pmod(hash(rand()),60)
;

